﻿using CrystalDecisions.Shared;
using KPIS.DBM;
using KPIS.GERP.GFMS.APP.App_UI.Commons;
using KPIS.GERP.GFMS.APP.Reports;
using KPIS.GERP.GFMS.DAL;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GHRMS.DAL;
using System;
using System.Data;
using System.Web.UI;
using Telerik.Web.UI;

namespace KPIS.GERP.GFMS.APP.App_UI.Forms
{
    public partial class FIM_Payrolls : System.Web.UI.Page
    {
        PayrollDAL payroll = new PayrollDAL();
        D_HRM_PS_EmployeeTypes et = new D_HRM_PS_EmployeeTypes();
        EmployeeDAL emp = new EmployeeDAL();
        Main main = new Main();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                LoadEmpTypeSeq();
                ddlMonth.SelectedValue = DateTime.Today.Month.ToString();
                txtYear.Text = (DateTime.Today.Year + 543).ToString();
                LoadGrdView();
                LoadEmpSeq(ddlEmpTypeSeq.SelectedValue);
                lblIncome.Text = "0.00";
                lblDeduct.Text = "0.00";
                lblNet.Text = "0.00";
            }
        }

        protected void LoadGrdView()
        {
            string strEmpTypeSeq = ddlEmpTypeSeq.SelectedValue;
            string strYear = txtYear.Text;
            string strMonth = ddlMonth.SelectedValue;

            if (string.IsNullOrEmpty(strEmpTypeSeq) || string.IsNullOrEmpty(strYear) || string.IsNullOrEmpty(strMonth)
                || strYear.Length != 4)
            {
                grdView.DataSource = null;
                grdView.DataBind();
                lblPaySeq.Text = "";
            }
            else
            {
                DataTable dtb = payroll.GetDataView(strEmpTypeSeq, strYear, strMonth);
                grdView.DataSource = dtb;
                grdView.DataBind();
                lblPaySeq.Text = dtb.Rows.Count > 0 ? dtb.Rows[0]["pay_seq"].ToString() : "";
            }
        }

        protected void LoadEmpTypeSeq()
        {
            ddlEmpTypeSeq.DataSource = et.GetList();
            ddlEmpTypeSeq.DataBind();

            ddlEmpTypeSeq.SelectedIndex = 0;
        }

        protected void LoadEmpSeq(string strEmpTypeSeq)
        {
            if (!string.IsNullOrEmpty(strEmpTypeSeq))
            {
                var empList = emp.PayrollEmployeesList(strEmpTypeSeq, txtYear.Text, ddlMonth.SelectedValue);
                ddlEmpSeq.DataSource = empList;
                ddlEmpSeq.DataBind();
            }
            else
            {
                ddlEmpSeq.Items.Clear();
            }
        }

        protected void LoadPayrollDtls(string pay_seq, string emp_seq)
        {
            DataTable dtbIncomeType = payroll.GetDataInfo(pay_seq, emp_seq, "I");
            DataTable dtbDeductType = payroll.GetDataInfo(pay_seq, emp_seq, "D");

            grdIncome.DataSource = dtbIncomeType;
            grdIncome.DataBind();
            grdDeduct.DataSource = dtbDeductType;
            grdDeduct.DataBind();
            txtAmount_TextChanged(null, null);
        }

        protected void grdView_ItemCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
        {
            if (e.CommandName == "Filter" || e.CommandName == "Sort" || e.CommandName == "Page")
            {
                LoadGrdView();
            }
            else if (e.CommandName == "GridEdit")
            {
                ddlEmpSeq.Visible = false;
                lblEmpName.Visible = true;
                cmdCalculate.Visible = true;
                cmdInsert.Visible = false;
                cmdUpdate.Visible = false;
                cmdDelete.Visible = false;

                GridDataItem item = e.Item as GridDataItem;

                lblPaySeq.Text = item["pay_seq"].Text;
                lblEmpSeq.Text = main.WebString(item["emp_seq"].Text);
                var empInfo = emp.PayrollEmployeesInfo(lblEmpSeq.Text, txtYear.Text, ddlMonth.SelectedValue);
                //empList.where

                //var empInfo = empList.Where(m => m.emp_seq == Convert.ToInt32(ddlEmpSeq.SelectedValue)).FirstOrDefault();

                lblEmpName.Text = empInfo.Rows[0]["emp_name"].ToString();
                lblPosName.Text = "[" + empInfo.Rows[0]["pos_ref"].ToString() + "] " + empInfo.Rows[0]["pos_name"].ToString();
                lblClassName.Text = empInfo.Rows[0]["class_name"].ToString();
                lblJobsiteName.Text = empInfo.Rows[0]["job_site_name"].ToString();
                //ddlEmpSeq.SelectedValue = main.WebString(item["emp_seq"].Text);
                //ddlEmpSeq_SelectedIndexChanged(null, null);
                LoadPayrollDtls(item["pay_seq"].Text, item["emp_seq"].Text);
            }
        }

        protected void cmdCopy_Click(object sender, EventArgs e)
        {
            int nMonthNo = 0;
            int nYear = 0;
            int nBaseMonthNo = 0;
            int nBaseYear = 0;
            int nUserSeq = new SsMng().EmpSeqI;

            if (!int.TryParse(ddlMonth.SelectedValue, out nMonthNo) || !int.TryParse(txtYear.Text, out nYear))
            {
                main.MessageAlert(Page, "กรุณาเลือกเดือนและปี");
                return;
            }

            string strPaySeq = payroll.FindPayroll(ddlEmpTypeSeq.SelectedValue, nYear.ToString(), nMonthNo.ToString());

            if (string.IsNullOrEmpty(strPaySeq))
            {
                strPaySeq = payroll.InsertPayroll(Convert.ToInt32(ddlEmpTypeSeq.SelectedValue), nYear, nMonthNo, nUserSeq).ToString();
            }

            if (nMonthNo == 1)
            {
                nBaseMonthNo = 12;
                nBaseYear = nYear - 1;
            }
            else
            {
                nBaseMonthNo = nMonthNo - 1;
                nBaseYear = nYear;
            }

            int nPaySeq = Convert.ToInt32(strPaySeq);

            payroll.DeletePayrollDtls(nPaySeq, null, nUserSeq);

            DataTable dtb = payroll.GetDtlsData(ddlEmpTypeSeq.SelectedValue, nBaseYear.ToString(), nBaseMonthNo.ToString());

            foreach (DataRow dr in dtb.Rows)
            {
                payroll.InsertPayrollDtls(nPaySeq, Convert.ToInt32(dr["emp_seq"]), Convert.ToInt32(dr["ext_seq"]), main.ConvertNullDouble(dr["amount"]) ?? 0, nUserSeq);
            }

            LoadGrdView();
            main.MessageAlert(Page, "บันทึกเรียบร้อย");
        }

        protected void ddlEmpTypeSeq_SelectedIndexChanged(object sender, Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs e)
        {
            LoadEmpSeq(ddlEmpTypeSeq.SelectedValue);
            LoadGrdView();
        }

        protected void txtYear_TextChanged(object sender, EventArgs e)
        {
            LoadGrdView();
        }

        protected void ddlMonth_SelectedIndexChanged(object sender, Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs e)
        {
            LoadGrdView();
        }

        protected void cmdAddNew_Click(object sender, EventArgs e)
        {
            LoadEmpSeq(ddlEmpTypeSeq.SelectedValue);
            ddlEmpSeq.Visible = true;
            lblEmpName.Visible = false;
            lblEmpSeq.Text = "";
            cmdCalculate.Visible = true;
            cmdInsert.Visible = false;
            cmdUpdate.Visible = false;
            cmdDelete.Visible = false;

            LoadPayrollDtls(lblPaySeq.Text, null);
            lblIncome.Text = "0.00";
            lblDeduct.Text = "0.00";
            lblNet.Text = "0.00";
        }

        protected void cmdAddNewAll_Click(object sender, EventArgs e)
        {

        }

        protected void RadAjaxManager1_AjaxRequest(object sender, Telerik.Web.UI.AjaxRequestEventArgs e)
        {
            LoadGrdView();
        }

        protected void ddlEmpSeq_SelectedIndexChanged(object sender, Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs e)
        {
            if (!string.IsNullOrEmpty(ddlEmpSeq.SelectedValue))
            {
                //var empList = emp.PayrollEmployeesList(ddlEmpTypeSeq.SelectedValue, txtYear.Text, ddlMonth.SelectedValue);
                var empInfo = emp.PayrollEmployeesInfo(ddlEmpSeq.SelectedValue, txtYear.Text, ddlMonth.SelectedValue);

                lblPosName.Text = "[" + empInfo.Rows[0]["pos_ref"].ToString() + "] " + empInfo.Rows[0]["pos_name"].ToString();
                lblClassName.Text = empInfo.Rows[0]["class_name"].ToString();
                lblJobsiteName.Text = empInfo.Rows[0]["job_site_name"].ToString();

                foreach (GridDataItem item in grdIncome.Items)
                {
                    string cmd_type_seq = main.WebString(item["cmd_type_seq"].Text);

                    if (cmd_type_seq == "1")
                    {
                        (item["amount"].FindControl("txtAmount") as RadNumericTextBox).Text = (main.ConvertNullDouble(empInfo.Rows[0]["salary"]) ?? 0).ToString("#,##0.00");
                    }
                    else if (cmd_type_seq == "2")
                    {
                        (item["amount"].FindControl("txtAmount") as RadNumericTextBox).Text = (main.ConvertNullDouble(empInfo.Rows[0]["pos_exec_salary"]) ?? 0).ToString("#,##0.00");
                    }
                    else if (cmd_type_seq == "3")
                    {

                    }
                    else
                    {
                        (item["amount"].FindControl("txtAmount") as RadNumericTextBox).Text = "0.00";
                    }
                }
            }
            else
            {
                lblPosName.Text = "";
                lblClassName.Text = "";
                lblJobsiteName.Text = "";
            }
        }

        protected void cmdInsert_Click(object sender, EventArgs e)
        {
            int nEmpSeq = main.ConvertNullInt(ddlEmpSeq.SelectedValue) ?? -1;
            int nUserSeq = new SsMng().EmpSeqI;

            if (nEmpSeq == -1)
            {
                main.MessageAlert(Page, "กรุณาเลือกบุคลากรที่จะทำรายการ");
                return;
            }

            if (string.IsNullOrEmpty(lblPaySeq.Text))
            {
                int emp_type;
                int year;
                int month;

                if (!int.TryParse(ddlEmpTypeSeq.SelectedValue, out emp_type) || !int.TryParse(txtYear.Text, out year) || !int.TryParse(ddlMonth.SelectedValue, out month))
                {
                    //throw new Exception("ข้อมูลไม่ถูกต้อง");
                    main.MessageAlert(Page, "ข้อมูลไม่ถูกต้อง");
                    return;
                }

                lblPaySeq.Text = payroll.InsertPayroll(emp_type, year, month, nUserSeq).ToString();
            }

            foreach (GridDataItem item in grdIncome.Items)
            {
                int nExtSeq = main.ConvertNullInt(main.WebString(item["ext_seq"].Text)) ?? -1;
                double dAmount = (item["amount"].FindControl("txtAmount") as RadNumericTextBox).Value ?? 0;

                payroll.InsertPayrollDtls(Convert.ToInt32(lblPaySeq.Text), nEmpSeq, nExtSeq, dAmount, nUserSeq);
            }

            foreach (GridDataItem item in grdDeduct.Items)
            {
                int nExtSeq = main.ConvertNullInt(main.WebString(item["ext_seq"].Text)) ?? -1;
                double dAmount = (item["amount"].FindControl("txtAmount") as RadNumericTextBox).Value ?? 0;

                payroll.InsertPayrollDtls(Convert.ToInt32(lblPaySeq.Text), nEmpSeq, nExtSeq, dAmount, nUserSeq);
            }

            LoadGrdView();
            main.MessageAlert(Page, "บันทึกเรียบร้อย");
        }

        protected void cmdUpdate_Click(object sender, EventArgs e)
        {
            int nEmpSeq = main.ConvertNullInt(lblEmpSeq.Text) ?? -1;
            int nUserSeq = new SsMng().EmpSeqI;

            if (nEmpSeq == -1)
            {
                main.MessageAlert(Page, "กรุณาเลือกบุคลากรที่จะทำรายการ");
                return;
            }

            foreach (GridDataItem item in grdIncome.Items)
            {
                int? nPayrollDtlsSeq = main.ConvertNullInt(main.WebString(item["pay_dtls_seq"].Text));
                int nExtSeq = main.ConvertNullInt(main.WebString(item["ext_seq"].Text)) ?? -1;
                double dAmount = (item["amount"].FindControl("txtAmount") as RadNumericTextBox).Value ?? 0;

                if (nPayrollDtlsSeq.HasValue)
                {
                    payroll.EditPayrollDtls(nPayrollDtlsSeq.Value, dAmount, nUserSeq);
                }
                else
                {
                    payroll.InsertPayrollDtls(Convert.ToInt32(lblPaySeq.Text), nEmpSeq, nExtSeq, dAmount, nUserSeq);
                }
            }

            foreach (GridDataItem item in grdDeduct.Items)
            {
                int? nPayrollDtlsSeq = main.ConvertNullInt(main.WebString(item["pay_dtls_seq"].Text));
                int nExtSeq = main.ConvertNullInt(main.WebString(item["ext_seq"].Text)) ?? -1;
                double dAmount = (item["amount"].FindControl("txtAmount") as RadNumericTextBox).Value ?? 0;

                if (nPayrollDtlsSeq.HasValue)
                {
                    payroll.EditPayrollDtls(nPayrollDtlsSeq.Value, dAmount, nUserSeq);
                }
                else
                {
                    payroll.InsertPayrollDtls(Convert.ToInt32(lblPaySeq.Text), nEmpSeq, nExtSeq, dAmount, nUserSeq);
                }
            }

            LoadGrdView();
            main.MessageAlert(Page, "บันทึกเรียบร้อย");
            ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "close", "closeEdit();", true);
        }

        protected void cmdDelete_Click(object sender, EventArgs e)
        {
            if (main.ConvertNullInt(lblEmpSeq.Text) == null || main.ConvertNullInt(lblEmpSeq.Text) == null)
            {
                return;
            }

            payroll.DeletePayrollDtls(Convert.ToInt32(lblPaySeq.Text), main.ConvertNullInt(lblEmpSeq.Text) ?? -1, new SsMng().EmpSeqI);
            LoadGrdView();
            main.MessageAlert(Page, "บันทึกเรียบร้อย");
            ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "close", "closeEdit();", true);
        }

        protected void txtAmount_TextChanged(object sender, EventArgs e)
        {
            double dIncome = 0;
            double dDeduct = 0;

            foreach (GridDataItem item in grdIncome.Items)
            {
                dIncome += (item["amount"].FindControl("txtAmount") as RadNumericTextBox).Value ?? 0;
            }

            foreach (GridDataItem item in grdDeduct.Items)
            {
                dDeduct += (item["amount"].FindControl("txtAmount") as RadNumericTextBox).Value ?? 0;
            }

            lblIncome.Text = dIncome.ToString("#,##0.00");
            lblDeduct.Text = dDeduct.ToString("#,##0.00");
            lblNet.Text = (dIncome - dDeduct).ToString("#,##0.00");
        }

        protected void cmdCalculate_Click(object sender, EventArgs e)
        {
            //cmdCalculate.Visible = false;

            bool bEdit = !string.IsNullOrEmpty(lblEmpSeq.Text);
            cmdInsert.Visible = !bEdit;
            cmdUpdate.Visible = bEdit;
            cmdDelete.Visible = bEdit;

            double dIncome = 0;
            double dDeduct = 0;

            foreach (GridDataItem item in grdIncome.Items)
            {
                dIncome += (item["amount"].FindControl("txtAmount") as RadNumericTextBox).Value ?? 0;
            }

            foreach (GridDataItem item in grdDeduct.Items)
            {
                dDeduct += (item["amount"].FindControl("txtAmount") as RadNumericTextBox).Value ?? 0;
            }

            lblIncome.Text = dIncome.ToString("#,##0.00");
            lblDeduct.Text = dDeduct.ToString("#,##0.00");
            lblNet.Text = (dIncome - dDeduct).ToString("#,##0.00");
        }

        protected void LinkButton1_Click(object sender, EventArgs e)
        {
            switch (ddlReport.SelectedValue)
            {
                case "Payroll01":
                    Payroll01();
                    break;
                case "Payroll02":
                    Payroll02();
                    break;
                case "Payroll03":
                    Payroll03();
                    break;
                default:
                    break;
            }

        }

        private void Payroll01()
        {
            DBManager dbmanger = new DBManager(DataProvider.SqlServer, SystemInfo._ConnectionString);

            try
            {
                dbmanger.Open();
                dbmanger.CreateParameters(3);
                dbmanger.AddParameters(0, "@emp_type_seq", ddlEmpTypeSeq.SelectedValue);
                dbmanger.AddParameters(1, "@pay_year", txtYear.Text);
                dbmanger.AddParameters(2, "@pay_month", ddlMonth.SelectedValue);
                string strSQL = @"
select pr.pay_seq,dtls.ext_seq, ext.ext_name,ext.ext_code, SUM(COALESCE(amount,0)) as amount 
,CASE ext.ext_flag
         WHEN 'I' THEN '1'
         WHEN 'D' THEN '2'
         WHEN 'E' THEN '3'
         ELSE '4'
      END as ext_flag
,CASE ext_flag 
         WHEN 'I' THEN 'รายรับ'  
         WHEN 'D' THEN 'เงินหัก'  
         WHEN 'E' THEN 'เงินสมทบและเงินชดเชย'  
         ELSE ''  
      END as ext_flag_name
from hrm_pr_payrolls as pr
LEFT JOIN hrm_pr_payroll_dtls dtls ON pr.pay_seq = dtls.pay_seq
LEFT JOIN hrm_pr_extra_types ext ON dtls.ext_seq = ext.ext_seq
where pr.emp_type_seq  = @emp_type_seq
and pr.pay_month  = @pay_month
and pr.pay_year = @pay_year
GROUP BY pr.pay_seq ,dtls.ext_seq , ext.ext_name,ext.ext_flag,ext.ext_code
ORDER BY ext.ext_code
;
";
                DataTable dtb = dbmanger.ExecuteDataTable(CommandType.Text, strSQL);
                Payroll01 payroll01 = new Payroll01();
                payroll01.SetDataSource(dtb);
                payroll01.SetParameterValue(0, "สำนักงานปลัดกระทรวงการคลัง");
                payroll01.SetParameterValue(1, string.Format("ประจำเดือน {1} ปี พ.ศ. {0}", txtYear.Text, ddlMonth.Items[ddlMonth.SelectedIndex].Text));

                Response.Buffer = false;
                Response.ClearContent();
                Response.ContentType = "application/pdf";
                Response.ClearHeaders();
                payroll01.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, "Payroll01");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbmanger.Close();
            }
        }

        private void Payroll02()
        {
            DBManager dbmanger = new DBManager(DataProvider.SqlServer, SystemInfo._ConnectionString);

            try
            {
                dbmanger.Open();
                dbmanger.CreateParameters(3);
                dbmanger.AddParameters(0, "@emp_type_seq", ddlEmpTypeSeq.SelectedValue);
                dbmanger.AddParameters(1, "@pay_year", txtYear.Text);
                dbmanger.AddParameters(2, "@pay_month", ddlMonth.SelectedValue);


                string strSQL = @"
SELECT emp.emp_seq,emp.citizen, null as account_no, concat(mp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name, j.job_site_name
, j.job_site_seq, j.job_site_level_seq, j.job_site_full_path, j.job_site_ref, pos.pos_ref
, sum(case when ext.ext_flag = 'I' and ext.cmd_type_seq = 1 then prd.amount else 0 end) as salary
, sum(case when ext.ext_flag = 'D' and ext.cmd_type_seq = 1 then prd.amount else 0 end) as tax
, sum(case ext.ext_flag WHEN 'I' THEN prd.amount
		WHEN 'D' THEN -prd.amount
		WHEN 'E' THEN 0
		ELSE 0
		END) AS net
FROM hrm_pr_payrolls pr 
LEFT JOIN hrm_pr_payroll_dtls prd ON pr.pay_seq = prd.pay_seq
LEFT JOIN hrm_ps_employees emp ON prd.emp_seq = emp.emp_seq
LEFT JOIN mas_name_prefixes mp ON mp.name_prefix_seq = emp.name_prefix_seq
LEFT JOIN hrm_pr_extra_types ext ON prd.ext_seq = ext.ext_seq
LEFT JOIN hrm_mp_job_sites j ON j.job_site_seq = dbo.f_get_job_site_by_level(emp.working_job_site_seq, 3)
LEFT JOIN hrm_ps_emp_book_banks book ON book.emp_seq = emp.emp_seq
LEFT JOIN hrm_mp_positions pos ON emp.pos_seq = pos.pos_seq
WHERE pr.emp_type_seq = @emp_type_seq 
AND pr.pay_month = @pay_month
AND pr.pay_year = @pay_year
GROUP BY emp.emp_seq, emp.citizen, book.bank_acc_no, mp.prefix_name, emp.first_name, emp.last_name, j.job_site_ref, j.job_site_name, j.job_site_seq, j.job_site_level_seq, j.job_site_full_path, pos.pos_ref
order by j.job_site_ref, pos.pos_ref
";
                DataTable dtb = dbmanger.ExecuteDataTable(CommandType.Text, strSQL);
//                strSQL = @"
//select j.job_site_name, j.job_site_seq, j.job_site_full_path, j.job_site_ref
//	from hrm_mp_job_sites j
//	where j.job_site_seq = job_site_seq
//";

//                foreach (DataRow dr in dtb.Rows)
//                {
//                    if ( Convert.ToInt32(dr["job_site_level_seq"]) > 3)
//                    {
//                        string strJobsiteSeq = dr["job_site_full_path"].ToString().Split('/')[1];

//                        dbmanger.CreateParameters(1);
//                        dbmanger.AddParameters(0, "@job_site_seq", strJobsiteSeq);

//                        DataTable dtb2 = dbmanger.ExecuteDataTable(CommandType.Text, strSQL);

//                        if (dtb2.Rows.Count > 0)
//                        {
//                            dr["job_site_seq"] = dtb2.Rows[0]["job_site_seq"].ToString();
//                            dr["job_site_ref"] = dtb2.Rows[1]["job_site_ref"].ToString();
//                            dr["job_site_name"] = dtb2.Rows[1]["job_site_name"].ToString();
//                            dr["job_site_full_path"] = dtb2.Rows[2]["job_site_full_path"].ToString();
//                        }
//                    }
//                }

                Payroll02 payroll02 = new Payroll02();
                payroll02.SetDataSource(dtb);

                dbmanger.CreateParameters(3);
                dbmanger.AddParameters(0, "@emp_type_seq", ddlEmpTypeSeq.SelectedValue);
                dbmanger.AddParameters(1, "@pay_year", txtYear.Text);
                dbmanger.AddParameters(2, "@pay_month", ddlMonth.SelectedValue);

                string filter1 = "('I','E')";
                string strSQLSub01 = @"
SELECT dt.emp_seq, ext.ext_flag, ext.ext_code, ext.ext_name, dt.amount
FROM hrm_pr_payrolls pay
LEFT JOIN hrm_pr_payroll_dtls dt ON pay.pay_seq = dt.pay_seq
LEFT JOIN hrm_pr_extra_types ext ON dt.ext_seq = ext.ext_seq
WHERE pay.record_status <> 'D' 
AND pay.emp_type_seq = @emp_type_seq
AND pay.pay_year = @pay_year
AND pay.pay_month = @pay_month
AND dt.amount > 0
and coalesce(ext.cmd_type_seq, -1) <> 1
AND ext.ext_flag IN " + filter1 + @"
ORDER BY dt.emp_seq, ext.ext_code
";
                DataTable dtbSub01 = dbmanger.ExecuteDataTable(CommandType.Text, strSQLSub01);
                payroll02.Subreports[0].SetDataSource(dtbSub01);

                string filter2 = "('D')";
                string strSQLSub02 = @"
SELECT dt.emp_seq, ext.ext_flag, ext.ext_code, ext.ext_name, dt.amount
FROM hrm_pr_payrolls pay
LEFT JOIN hrm_pr_payroll_dtls dt ON pay.pay_seq = dt.pay_seq
LEFT JOIN hrm_pr_extra_types ext ON dt.ext_seq = ext.ext_seq
WHERE pay.record_status <> 'D'
AND pay.emp_type_seq = @emp_type_seq
AND pay.pay_year = @pay_year
AND pay.pay_month = @pay_month
AND dt.amount > 0
and coalesce(ext.cmd_type_seq, -1) <> 1
AND ext.ext_flag IN " + filter2 + @"
ORDER BY dt.emp_seq, ext.ext_code
";
                DataTable dtbSub02 = dbmanger.ExecuteDataTable(CommandType.Text, strSQLSub02);
                payroll02.Subreports[1].SetDataSource(dtbSub02);

                string filter3 = "('I','E')";
                string strSQLSub03 = @"
select job_site_seq, ext_flag, ext_code, ext_name, sum(coalesce(amount, 0)) as amount
from (
SELECT dbo.f_get_job_site_by_level(emp.working_job_site_seq, 3) as job_site_seq, ext.ext_flag, ext.ext_code, ext.ext_name, dt.amount as amount
FROM hrm_pr_payrolls pay
LEFT JOIN hrm_pr_payroll_dtls dt ON pay.pay_seq = dt.pay_seq
LEFT JOIN hrm_pr_extra_types ext ON dt.ext_seq = ext.ext_seq
LEFT JOIN hrm_ps_employees emp ON dt.emp_seq = emp.emp_seq
WHERE pay.record_status <> 'D'
AND pay.emp_type_seq = @emp_type_seq
AND pay.pay_year = @pay_year
AND pay.pay_month = @pay_month
AND dt.amount > 0
and coalesce(ext.cmd_type_seq, -1) <> 1
AND ext.ext_flag IN " + filter3 + @"
) t
group by job_site_seq, ext_flag, ext_code, ext_name
ORDER BY job_site_seq, ext_code
";
                DataTable dtbSub03 = dbmanger.ExecuteDataTable(CommandType.Text, strSQLSub03);
                payroll02.Subreports[2].SetDataSource(dtbSub03);

                string filter4 = "('I','E')";
                string strSQLSub04 = @"
select job_site_seq, ext_flag, ext_code, ext_name, sum(coalesce(amount, 0)) as amount
from (
SELECT dbo.f_get_job_site_by_level(emp.working_job_site_seq, 3) as job_site_seq, ext.ext_flag, ext.ext_code, ext.ext_name, dt.amount as amount
FROM hrm_pr_payrolls pay
LEFT JOIN hrm_pr_payroll_dtls dt ON pay.pay_seq = dt.pay_seq
LEFT JOIN hrm_pr_extra_types ext ON dt.ext_seq = ext.ext_seq
LEFT JOIN hrm_ps_employees emp ON dt.emp_seq = emp.emp_seq
WHERE pay.record_status <> 'D'
AND pay.emp_type_seq = @emp_type_seq
AND pay.pay_year = @pay_year
AND pay.pay_month = @pay_month
AND dt.amount > 0
and coalesce(ext.cmd_type_seq, -1) <> 1
AND ext.ext_flag IN " + filter4 + @"
) t
group by job_site_seq, ext_flag, ext_code, ext_name
ORDER BY job_site_seq, ext_code
";
                DataTable dtbSub04 = dbmanger.ExecuteDataTable(CommandType.Text, strSQLSub04);
                payroll02.Subreports[3].SetDataSource(dtbSub04);



                //payroll02.SetParameterValue(0, "สำนักงานปลัดกระทรวงการคลัง");
                //payroll02.SetParameterValue(1, string.Format("ประจำเดือน {1} ปี พ.ศ. {0}", txtYear.Text, ddlMonth.Items[ddlMonth.SelectedIndex].Text));
                Response.Buffer = false;
                Response.ClearContent();
                Response.ContentType = "application/pdf";
                Response.ClearHeaders();
                payroll02.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, "Payroll02");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbmanger.Close();
            }
        }

        private void Payroll03()
        {
            DBManager dbmanger = new DBManager(DataProvider.SqlServer, SystemInfo._ConnectionString);

            try
            {
                dbmanger.Open();
                dbmanger.CreateParameters(3);
                dbmanger.AddParameters(0, "@emp_type_seq", ddlEmpTypeSeq.SelectedValue);
                dbmanger.AddParameters(1, "@pay_year", txtYear.Text);
                dbmanger.AddParameters(2, "@pay_month", ddlMonth.SelectedValue);

                string strSQL = @"
SELECT emp.emp_seq, emp.citizen, concat(mp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name ,j.job_site_ref, j.job_site_name as job_site_name, 
prd.amount as salary, prd2.amount as amount_prd2_new, prd3.amount as amount_prd3_new, prd4.amount as amount_prd4_new, prd5.amount as amount_prd5_new,
pr2d2.amount as amount_pr2d2_old, pr2d3.amount as amount_pr2d3_old, pr2d4.amount as amount_pr2d4_old, pr2d5.amount as amount_pr2d5_old
FROM hrm_pr_payrolls pr 
LEFT JOIN hrm_pr_payroll_dtls prd ON pr.pay_seq = prd.pay_seq AND prd.ext_seq = 1
LEFT JOIN hrm_pr_payroll_dtls prd2 ON pr.pay_seq = prd2.pay_seq AND prd2.ext_seq = 74 AND prd.emp_seq = prd2.emp_seq
LEFT JOIN hrm_pr_payroll_dtls prd3 ON pr.pay_seq = prd3.pay_seq AND prd3.ext_seq = 16 AND prd.emp_seq = prd3.emp_seq
LEFT JOIN hrm_pr_payroll_dtls prd4 ON pr.pay_seq = prd4.pay_seq AND prd4.ext_seq = 7 AND prd.emp_seq = prd4.emp_seq
LEFT JOIN hrm_pr_payroll_dtls prd5 ON pr.pay_seq = prd5.pay_seq AND prd5.ext_seq = 75 AND prd.emp_seq = prd5.emp_seq

LEFT JOIN hrm_pr_payrolls pr2 ON pr2.record_status <> 'D' AND 
pr2.pay_year = (CASE WHEN pr.pay_month = 12 THEN pr.pay_year -1 ELSE pr.pay_year END) AND 
pr2.pay_month = (CASE WHEN pr.pay_month = 12 THEN 1 ELSE pr.pay_month -1 END)
LEFT JOIN hrm_pr_payroll_dtls pr2d ON pr2.pay_seq = pr2d.pay_seq AND prd2.ext_seq = 1 AND prd.emp_seq = pr2d.emp_seq
LEFT JOIN hrm_pr_payroll_dtls pr2d2 ON pr2.pay_seq = pr2d2.pay_seq AND pr2d2.ext_seq = 74 AND prd.emp_seq = pr2d2.emp_seq
LEFT JOIN hrm_pr_payroll_dtls pr2d3 ON pr2.pay_seq = pr2d3.pay_seq AND pr2d3.ext_seq = 16 AND prd.emp_seq = pr2d3.emp_seq
LEFT JOIN hrm_pr_payroll_dtls pr2d4 ON pr2.pay_seq = pr2d4.pay_seq AND pr2d4.ext_seq = 7 AND prd.emp_seq = pr2d4.emp_seq
LEFT JOIN hrm_pr_payroll_dtls pr2d5 ON pr2.pay_seq = pr2d5.pay_seq AND pr2d5.ext_seq = 75 AND prd.emp_seq = pr2d5.emp_seq

LEFT JOIN hrm_ps_employees emp ON prd.emp_seq = emp.emp_seq
LEFT JOIN mas_name_prefixes mp ON mp.name_prefix_seq = emp.name_prefix_seq
LEFT JOIN hrm_pr_extra_types ext ON prd.ext_seq = ext.ext_seq
LEFT JOIN hrm_mp_job_sites j ON j.job_site_seq = dbo.f_get_job_site_by_level(emp.working_job_site_seq, 3)
LEFT JOIN hrm_mp_positions p ON p.pos_seq = emp.pos_seq
WHERE pr.record_status <> 'D' AND prd.record_status <> 'D' AND pr.pay_month = @pay_month AND pr.pay_year = @pay_year
ORDER BY j.job_site_ref, p.pos_ref
";
                DataTable dtb = dbmanger.ExecuteDataTable(CommandType.Text, strSQL);
                Payroll03 payroll03 = new Payroll03();
                payroll03.SetDataSource(dtb);
                payroll03.SetParameterValue(0, string.Format("ประจำเดือน {1} ปี พ.ศ. {0}", txtYear.Text, ddlMonth.Items[ddlMonth.SelectedIndex].Text));

                Response.Buffer = false;
                Response.ClearContent();
                Response.ContentType = "application/pdf";
                Response.ClearHeaders();
                payroll03.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, "Payroll03");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbmanger.Close();
            }
        }


    }
}